import pymysql

class MySQLUtils:
    def __init__(self, host, port, user, password, database):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.connection.cursor()
            print("Connected to MySQL database")
        except Exception as e:
            print(f"Error connecting to MySQL database: {e}")

    def execute_insert(self, query, params):
        try:
            self.cursor.execute(query, params)
            self.connection.commit()
            print("Insert successful")
        except Exception as e:
            print(f"Error executing insert: {e}")
            self.connection.rollback()

    def execute_update(self, query):
        try:
            self.cursor.execute(query)
            self.connection.commit()
            print("Update successful")
        except Exception as e:
            self.connection.rollback()
            print(f"Error executing update: {e}")

    def close_connection(self):
        try:
            self.cursor.close()
            self.connection.close()
            print("Connection closed")
        except Exception as e:
            print(f"Error closing connection: {e}")

    def query_sales_by_date_range(self, start_date, end_date):
        try:
            # 构建查询语句
            query = "SELECT * FROM sales_data WHERE year BETWEEN %s AND %s"
            # 执行查询
            self.cursor.execute(query, (start_date, end_date))
            # 获取查询结果
            sales_data = self.cursor.fetchall()
            print("Sales data queried successfully")
            return sales_data
        except Exception as e:
            print(f"Error querying sales data: {e}")
            return None

    def sum_sales_by_car(self,start_date,end_date):
        try:
            # 构建查询语句
            query = "SELECT  date_str,  car, SUM(sales) AS total_sales FROM sum_sales_by_car WHERE  date_str BETWEEN %s AND %s GROUP BY  date_str, car ORDER BY date_str, total_sales DESC"
            # 执行查询
            self.cursor.execute(query, (start_date, end_date))
            # 获取查询结果
            sales_data = self.cursor.fetchall()
            print("Sales data queried successfully")
            return sales_data
        except Exception as e:
            print(f"Error querying sales data: {e}")
            return None

    def sum_automobilesales_by_year(self, start_date, end_date):
        try:
            # 查询汽车品牌列表
            query_automobile = "SELECT automobile FROM sum_automobilesales_by_car GROUP BY automobile"
            self.cursor.execute(query_automobile)
            automobile_data = self.cursor.fetchall()
            automobiles = [row['automobile'] for row in automobile_data]
            # 初始化结果字典
            result = []
            # 查询每个汽车品牌的起始日期和结束日期销售额
            for automobile in automobiles:
                # 查询起始日期销售额
                query_start = "SELECT sales FROM sum_automobilesales_by_car WHERE date_str = %s AND automobile = %s"
                self.cursor.execute(query_start, (start_date, automobile))
                total_sales_start = self.cursor.fetchone()
                # 查询结束日期销售额
                query_end = "SELECT sales FROM sum_automobilesales_by_car WHERE date_str = %s AND automobile = %s"
                self.cursor.execute(query_end, (end_date, automobile))
                total_sales_end = self.cursor.fetchone()
                print(total_sales_end)
                # 构建汽车品牌的起始日期和结束日期销售额字典
                result.append({
                    "automobile": automobile,
                    "start_date": total_sales_start['sales'] if total_sales_start else None,  # 直接取sales的值
                    "end_date": total_sales_end['sales'] if total_sales_end else None,  # 同上
                })
            print("Sales data queried successfully")
            return result
        except Exception as e:
            print(f"Error querying sales data: {e}")
            print(f"Exception object: {type(e)}")


    def getcarnamedata(self, start_date, end_date,carname):
        try:
            result=[]
            # 查询起始日期销售额
            query_start = "SELECT COALESCE(SUM(sales), 0) AS total_sales FROM sum_sales_by_car WHERE  date_str BETWEEN %s AND %s"
            self.cursor.execute(query_start, (start_date, end_date))
            total_sales_start = self.cursor.fetchone()
            # 查询结束日期销售额
            query_end = "SELECT COALESCE(SUM(sales), 0) AS total_sales FROM sum_sales_by_car WHERE car = %s AND date_str BETWEEN %s AND %s"
            self.cursor.execute(query_end, (carname,start_date, end_date))
            total_sales_end = self.cursor.fetchone()
            # 构建汽车品牌的起始日期和结束日期销售额字典
            result.append({
                "carname": carname,
                "all_carsalse":total_sales_start['total_sales'],
                "self_saless": total_sales_end['total_sales']
            })
            print("Sales data queried successfully")
            return result
        except Exception as e:
            print(f"Error querying sales data: {e}")
            print(f"Exception object: {type(e)}")
            return None

    def getallsmalldata(self,carname):
        try:
            # 构建查询语句
            query = "SELECT  date_str, SUM(sales) AS total_sales FROM sum_sales_by_car WHERE  car = %s GROUP BY  date_str ORDER BY date_str, total_sales DESC"
            # 执行查询
            self.cursor.execute(query, carname)
            # 获取查询结果
            sales_data = self.cursor.fetchall()
            print("Sales data queried successfully")
            return sales_data
        except Exception as e:
            print(f"Error querying sales data: {e}")
            return None








